insert overwrite table jms_dm.dm_sqs_receiver_user_order_complaint_cnt_dt  
select 
receiver_mobile_phone    --电话号
,receiver_name           --收件人姓名
,receiver_province_name  --收件省name
,receiver_province_id    --收件省id
,receiver_city_name      --收件城市id
,receiver_city_id        --收件城市name
,receiver_area_name      --收件区name
,receiver_area_id        --收件区id
,waybill_cnt             --进三个月快件数量     
,complaint_cnt           --投诉量
,qxt_waybill_cnt         --七星潭进三个月快件数量
,thd_waybill_cnt         --桃花岛进三个月快件数量
,zjs_waybill_cnt         --紫金山进三个月快件数量
,other_waybill_cnt       --紫金山进三个月快件数量
,is_new_user             --是否新用户
,complaint_time          --最新投诉时间
,input_time              --最新订单录入时间
,qxt_complaint_cnt         --七星潭进三个月投诉量
,thd_complaint_cnt         --桃花岛进三个月投诉量
,zjs_complaint_cnt         --紫金山进三个月投诉量
,other_complaint_cnt       --紫金山进三个月投诉量
,date(input_time) as date_time
,date(input_time) as dt 
FROM
(
select
 t2.receiver_mobile_phone
,t2.receiver_name
,t2.receiver_province_name
,t2.receiver_province_id
,t2.receiver_city_name
,t2.receiver_city_id
,t2.receiver_area_name
,t2.receiver_area_id
,count(if(t2.is_cnt=1,t2.waybill_no,null)) as waybill_cnt                                       --进三个月快件数量
,count(if(t2.is_cnt=1,t1.waybill_no,null)) as complaint_cnt                                     --投诉量
,count(if(t2.is_cnt=1 and order_source_code='D801',t2.waybill_no,null)) as qxt_waybill_cnt      --七星潭进三个月快件数量
,count(if(t2.is_cnt=1 and order_source_code='D09',t2.waybill_no,null)) as thd_waybill_cnt       --桃花岛进三个月快件数量
,count(if(t2.is_cnt=1 and order_source_code='D67',t2.waybill_no,null)) as zjs_waybill_cnt       --紫金山进三个月快件数量
,count(if(t2.is_cnt=1 and order_source_code='其他',t2.waybill_no,null)) as other_waybill_cnt    --紫金山进三个月快件数量
,if(count(1)=1,1,0) as is_new_user                                                              --是否新用户
,max(t1.create_time) as complaint_time                                                          --最新投诉时间
,max(t2.input_time)  as  input_time                                                             --最新订单录入时间
,count(if(t2.is_cnt=1 and order_source_code='D801',t1.waybill_no,null)) as qxt_complaint_cnt      --七星潭进三个月投诉量
,count(if(t2.is_cnt=1 and order_source_code='D09',t1.waybill_no,null)) as thd_complaint_cnt       --桃花岛进三个月投诉量
,count(if(t2.is_cnt=1 and order_source_code='D67',t1.waybill_no,null)) as zjs_complaint_cnt       --紫金山进三个月投诉量
,count(if(t2.is_cnt=1 and order_source_code='其他',t1.waybill_no,null)) as other_complaint_cnt    --紫金山进三个月投诉量
from 
(
    select 
    waybill_id as waybill_no
    ,concat(substr(receiver_mobile_phone,1,3) ,'****',substr(receiver_mobile_phone,length(receiver_mobile_phone)-3,4)) as receiver_mobile_phone
    ,concat(substr(receiver_name,1,1),'**') as receiver_name
    ,receiver_province_name
    ,receiver_province_id
    ,receiver_city_name
    ,receiver_city_id
    ,receiver_area_name
    ,receiver_area_id
    ,dt
    ,input_time
    ,if(order_source_code in ('D09','D67','D801'),order_source_code,'其他') as order_source_code --D09 桃花岛 ,D67 紫金山，D801 七星潭
    ,if(dt between date_add('{{ execution_date  | cst_ds }}',-90) and '{{ execution_date  | cst_ds }}',1,0) as is_cnt --是否统计件量
    from jms_dwd.dwd_yl_oms_oms_order_incre_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-180) and '{{ execution_date  | cst_ds }}' 
    and receiver_name is not null and receiver_mobile_phone is not null
    and receiver_province_id is not null and receiver_city_id is not null and receiver_area_id is not null
) t2 
left join 
(
    SELECT
    waybill_no,max(pdd_create_time) as create_time
    from
    jms_dm.dm_sqs_false_sign_detail_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-90) and '{{ execution_date  | cst_ds }}'
    group by waybill_no
)t1 on t1.waybill_no =t2.waybill_no 
group by t2.receiver_mobile_phone,t2.receiver_name,t2.receiver_province_id,t2.receiver_city_id,t2.receiver_area_id,t2.receiver_province_name,t2.receiver_city_name,t2.receiver_area_name
)tt where (complaint_cnt/waybill_cnt>0.05 or  is_new_user=1) and date(input_time)='{{ execution_date  | cst_ds }}'
distribute by pmod(hash(rand()), 30);